import org.postgresql.util.PGobject;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DbService {
    private static final Logger lgr = Logger.getLogger(DbService.class.getName());
    private static final Map<String, String> map = System.getenv();
    private final LinkedHashMap<String, String> linkedHashMapQuestion = createLinkedListQuestion();

    public LinkedHashMap<String, String> getLinkedHashMapQuestion() {
        return linkedHashMapQuestion;
    }

    public DbService() {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public void insertData(Entity entity) {

        String query = "INSERT INTO table1(json_id, mytype, created, json_id_data, original, originaljsondata, json_question, json_question_data) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection con = getConnection();
             PreparedStatement pst = con.prepareStatement(query)) {

            PGobject jsonId = new PGobject();
            jsonId.setType("json");
            jsonId.setValue(entity.getJsonId());

            PGobject jsonQuestion = new PGobject();
            jsonQuestion.setType("json");
            jsonQuestion.setValue(entity.getJsonQuestion());

            PGobject jsonOriginal = new PGobject();
            jsonOriginal.setType("json");
            jsonOriginal.setValue(entity.getOriginal());

            pst.setString(1, entity.getJsonId());
            pst.setString(2, entity.getType());
            pst.setTimestamp(3, Timestamp.valueOf(entity.getCreated()));
            pst.setObject(4, jsonId);
            pst.setString(5, entity.getOriginal());
            pst.setObject(6, jsonOriginal);
            pst.setObject(7, entity.getJsonQuestion());
            pst.setObject(8, jsonQuestion);

            pst.executeUpdate();

        } catch (SQLException ex) {
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }

    }

    public List<String> getData(String mytype, String start, String stop, String by) {

        String query = getQuery(mytype, start, stop, by);
        List<String> list = new ArrayList<>();

        try (
                Connection con = getConnection();
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery(query)
        ) {
            while (rs.next()) {
                list.add(rs.getString("data"));
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    public List<String> getOriginalData() {

        List<String> list = new ArrayList<>();

        try (
                Connection con = getConnection();
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery("select id, original from table1")
        ) {
            while (rs.next()) {
                list.add(rs.getString("original"));
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    private String getQuery(String myType, String start, String stop, String by) {
        String idOrQ;
        if (by != null && by.equals("id")) {
            idOrQ = "json_id";
        } else if (by != null && by.equals("question")) {
            idOrQ = "json_question";
        } else {
            throw new RuntimeException("'by' может быть 'id' или 'question'");
        }

        if (myType == null && start == null && stop == null) {
//            throw new RuntimeException("'mytype' не может быть пустым");
            return String.format("select id, %s as data from table1", idOrQ);
        } else if (myType != null && start == null && stop == null) {
            return String.format("select id, %s as data from table1 " +
                    "where myType = '%s'", idOrQ, myType);
        } else if (myType != null && start != null && stop == null) {
            LocalDateTime ldtStart = LocalDateTime.parse(start, DateTimeFormatter.ofPattern("dd-MM-yyyy_HH:mm:ss"));

            return String.format("select id, %s as data from table1 " +
                    "where myType = '%s' and created >= '%s'", idOrQ, myType, LDTtoString(ldtStart));
        } else {
            LocalDateTime ldtStart = LocalDateTime.parse(start, DateTimeFormatter.ofPattern("dd-MM-yyyy_HH:mm:ss"));
            LocalDateTime ldtStop = LocalDateTime.parse(stop, DateTimeFormatter.ofPattern("dd-MM-yyyy_HH:mm:ss"));
            return String.format("select id, %s as data from table1 " +
                    "where myType = '%s' and created between '%s' and '%s'", idOrQ, myType, LDTtoString(ldtStart), LDTtoString(ldtStop));
        }
        //        return null;
    }

    private Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(
                map.get("dburl")
                , map.get("dbuser")
                , map.get("dbpassword")
        );
        Statement st = connection.createStatement();
        ResultSet rs = st.executeQuery("SELECT VERSION()");

        if (rs.next()) {
            lgr.log(Level.INFO, rs.getString(1));
        }
        return connection;
    }

    private String LDTtoString(LocalDateTime dateTime) {
        // 2024-03-09 06:13:37
        return dateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }

    private LinkedHashMap<String, String> createLinkedListQuestion() {
        LinkedHashMap<String, String> linkedHashMap = new LinkedHashMap<>();
        try (
                Connection con = getConnection();
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery("select qid, question from table2 " +
                        "order BY num")
        ) {
            while (rs.next()) {
                linkedHashMap.put(
                        rs.getString("qid")
                        , rs.getString("question"));
            }
            return linkedHashMap;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}